home *** CD-ROM | disk | FTP | other *** search
/ Agent Central Host Computer / Agent - Central Host Computer.iso / _SETUP.1 / vclosing.sql < prev    next >
Text File  |  2000-05-12  |  2KB  |  70 lines
  1. /*
  2. This view lists all closed items.  It makes a union of the 3 types, each from its own table: normal closes from the cashclose table; unmatched probes from the prorphans table and unmatched counts from the mrorphans table.
  3. */
  4.  
  5. CREATE OR REPLACE VIEW VCLOSING AS 
  6. SELECT
  7. '1' TYPE, CLO.CLBATCH_NUM, CLO.CLOSE_DATE, cas.farebox_glid, CAS.CASHBOX_GLID,
  8. CAS.CBREMOV_DATE, CAS.CBCOUNT_DATE, CAS.PROBE_COIN_TOTALS, 
  9.  tok.sm_tokens probe_sm_token_totals, tok.lg_tokens probe_lg_token_totals, 
  10. CAS.PROBE_BILL_TOTALS, CAS.PROBE_AMT, CAS.COUNT_COIN_TOTALS,
  11. SUB.COIN_SEVEN * 100  count_sm_token_totals, SUB.COIN_eight * 100 count_lg_token_totals, 
  12. CAS.COUNT_BILL_TOTALS, CAS.COUNT_AMT, BUS.BUS_ID, cas.excp_num,
  13. cex.descr
  14. FROM
  15. FAREBOX fbo,
  16. BUS BUS,
  17. CLOSE_BATCH CLO,
  18. CASHCLOSE CAS,
  19. Vtokctcashclose tok, 
  20. SUBBATCH sub, 
  21. CLOSE_EXCP cex
  22. WHERE cas.farebox_glid = FBO.GLID  AND
  23. FBO.LOCATION = BUS.GLID AND
  24. ( CLO.CLBATCH_NUM=CAS.CLBATCH_NUM ) AND
  25. cas.SUBBATCH_SEQ   = SUB.SUBBATCH_SEQ AND
  26. cas.last_curstate = tok.last_curstate AND
  27. cas.excp_num = cex.num
  28. UNION
  29. SELECT
  30. '2' TYPE, CLO.CLBATCH_NUM, CLO.CLOSE_DATE, pro.farebox_glid, PRO.CASHBOX_GLID,
  31. PRO.CBREMOV_DATE, SYSDATE cbcount_date, PRO.PROBE_COIN_TOTALS,  
  32.  tok.sm_tokens probe_sm_token_totals, tok.lg_tokens probe_lg_token_totals, PRO.PROBE_BILL_TOTALS,
  33. PRO.PROBE_AMT, 0 count_coin_totals, 
  34. 0  count_sm_token_totals, 0 count_lg_token_totals, 
  35. 0 count_bill_totals, 0 count_amt,BUS.BUS_ID, pro.excp_num,
  36. cex.descr
  37. FROM
  38. FAREBOX fbo,
  39. BUS BUS,
  40. CLOSE_BATCH CLO,
  41. PRORPHANS PRO,
  42. vtokctprorphans tok, 
  43. CLOSE_EXCP cex
  44. WHERE pro.farebox_glid = fbo.glid AND
  45. FBO.LOCATION = BUS.GLID AND
  46. ( CLO.CLBATCH_NUM=PRO.CLBATCH_SEQ ) AND
  47. pro.last_curstate = tok.last_curstate AND
  48. pro.excp_num = cex.num
  49. UNION
  50. SELECT
  51. '3' TYPE, CLO.CLBATCH_NUM, CLO.CLOSE_DATE, 0 farebox_glid, MRO.CASHBOX_GLID,
  52. SYSDATE cbremove_date, MRO.CBCOUNT_DATE, 0 probe_coin_totals, 
  53. 0 probe_sm_token_totals, 0 probe_lg_token_totals,  0 probe_bill_totals,
  54. 0 probe_amt, MRO.COUNT_COIN_TOTALS, 
  55. SUB.COIN_SEVEN * 100  count_sm_token_totals, SUB.COIN_eight * 100 count_lg_token_totals, 
  56. MRO.COUNT_BILL_TOTALS,
  57. MRO.COUNT_AMT , 0 bus_id, mro.excp_num,
  58. cex.descr
  59. FROM
  60. CLOSE_BATCH CLO,
  61. MRORPHANS MRO,
  62. SUBBATCH sub, 
  63. CLOSE_EXCP cex
  64. WHERE
  65. ( CLO.CLBATCH_NUM=MRO.CLBATCH_NUM )AND
  66. mro.SUBBATCH_SEQ   = SUB.SUBBATCH_SEQ AND
  67. mro.excp_num = cex.num;
  68.  
  69.  
  70.